<?php
require_once 'classes/PHPExcel.php';

function expExcel($arr, $fields)
{

    //实例化

    $objPHPExcel = new PHPExcel();
    /*右键属性所显示的信息*/
    $objPHPExcel->getProperties()->setCreator("yzs1013")//作者
    ->setLastModifiedBy("yzs1013")//最后一次保存者
    ->setTitle('数据EXCEL导出')//标题
    ->setSubject('数据EXCEL导出')//主题
    ->setDescription('导出数据')//描述
    ->setKeywords("excel")//标记
    ->setCategory("result file");  //类别

    $sarr = "_ABCDEFGHIJKLMNOPQRSTUVWXYZ";
    $objPHPExcel->setActiveSheetIndex(0);
    $execstr = "\$objPHPExcel->getActiveSheet()";
    $lineEnd = "";

    for ($i = 0; $i < count($fields); $i++) {
        $N=$sarr[$i+1];
        $execstr .= "->setCellValue('{$N}1', '{$fields[$i]}')";
        $lineEnd = $i+1;
    }
    $execstr .= "->getStyle('A1:{$sarr[$lineEnd]}1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);";
    eval($execstr);

    //设置宽度

    for ($i = 0; $i < count($fields); $i++) {
        $N=$sarr[$i+1];
        $execstr = "\$objPHPExcel->getActiveSheet()";
        $execstr .= "->getColumnDimension('{$N}')->setAutoSize(true);";
        eval($execstr.";");
    }


    $key = 1;
    foreach ($arr as $v) {
        $key++;
        $execstr = "\$objPHPExcel->getActiveSheet()";
        for ($i = 0; $i < count($fields); $i++) {
            $N=$sarr[$i+1];
            $execstr .= "->setCellValue('{$N}{$key}', '{$v[$fields[$i]]}')";

        }
        eval($execstr.";");
    }


    //设置当前的表格
    $objPHPExcel->setActiveSheetIndex(0);
    ob_end_clean();  //清除缓冲区,避免乱码
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
    header("Content-Type:application/force-download");
    header("Content-Type:application/vnd.ms-execl");
    header("Content-Type:application/octet-stream");
    header("Content-Type:application/download");
    header('Content-Disposition:attachment;filename="abc.xls"');
    header("Content-Transfer-Encoding:binary");

    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  //excel 2003
    $objWriter->save('php://output');
    exit(0);
}


if($_REQUEST['B1']) {
    $link = mysqli_connect('localhost', 'root', '159456') or die('连接数据库失败');
    mysqli_select_db($link, 'yun');
    mysqli_query($link, 'set names utf8');

    //$sql = "SELECT id,create_time,update_time,dateline,laiyuan FROM `yun_longzs` WHERE (  favid='70' ) ORDER BY `dateline` DESC,`id` DESC LIMIT 0,10";
    $sql = urldecode(trim($_REQUEST['sql']));
    if(preg_match("/^select\s(.*)from[^\;\-]*$/i", trim($sql), $nn)) {
        $fields = preg_split("/,/", trim($nn[1]));
        for($i=0;$i<count($fields);$i++){
            $fields[$i]=preg_replace("/^.*\s/","",$fields[$i]);
        }
        $res = mysqli_query($link, $sql);
        $arr = array();
        while ($row = mysqli_fetch_array($res, 1)) {
            $arr[] = $row;
        }
    }

    $name = "临时表";
    expExcel($arr, $fields);
}
?>
<!DOCTYPE html>
<html lang="zh-CN">
<head>
<body>
<header>
    <form name="sql" action="" method="post">
        从mysqlsql提取数据生成execl文件:<p>
        <textarea name="sql" style="width:95%;height:500px"><?php
            if($_REQUEST['sql']==""){
                echo "SELECT id,FROM_UNIXTIME(create_time) create_time,update_time,dateline,laiyuan FROM yun.`yun_longzs` WHERE (  favid='70' ) ORDER BY `dateline` DESC,`id` DESC LIMIT 0,10";
            }else {
                echo trim($_REQUEST['sql']);
            }

        ?></textarea><p>
        <input type="submit" name="B1">
        <pre>
            =TEXT((B2+8*3600)/86400+70*365+19,"yyyy-mm-dd hh:mm:ss")
        </pre>
    </form>

</header>

